import random

import pymysql
import pandas as pd
from datetime import datetime,timedelta
from db_conn.dbConn import conn_db,conn_db_engine
import pandas as pd
import time
from sqlalchemy import create_engine
from db_conn.dbConn import conn_db_engine
conn1 = conn_db_engine('iot_local')
conn2 = conn_db_engine('ryerp_local')
connect = conn_db('ryerp_local')


def socks_production(now):
    if now.hour >= 7:
        zero_today = now.replace(hour=7, minute=0, second=0, microsecond=0)
    else:
        zero_today = now.replace(hour=7, minute=0, second=0, microsecond=0) - pd.Timedelta(days=1)
    today = zero_today.date()
    df_socks_today = pd.read_sql("SELECT device_id,operate_time,goods_id,goods_style,batch_id,goods_id_old,goods_style_old,batch_id_old FROM mm_new_device_log where operate_time >= %s ", conn2,params=[(zero_today,)])
    df_socks_pre = pd.read_sql("SELECT device_id,goods_id,goods_style,batch_id FROM mm_new_device where operate_time < %s ", conn2,params=[(zero_today,)])
    df_socks_device = pd.read_sql("SELECT goods_id,goods_style,batch_id,avg_time FROM mm_new_device_vision where device_id < 253", conn2)
    df_socks_device_2 = pd.read_sql("SELECT goods_id,goods_style,batch_id,avg_time FROM mm_new_device_vision_2 where device_id >= 253", conn2)
    df_socks_device = pd.concat([df_socks_device, df_socks_device_2])
    df_iot = pd.read_sql('SELECT dev,num,receive_time,grp FROM iotdemo WHERE receive_time >= %s and type = 1',con=conn1,params=(zero_today,))
    #去掉df_iot中grp为6，dev为144的数据
    df_iot = df_iot[~((df_iot['grp'] == 6) & (df_iot['dev'] == 144))]
    #去掉df_iot中dev为169,170,171且num<20000的数据
    df_iot = df_iot[~(((df_iot['dev'] == 169) | (df_iot['dev'] == 170) | (df_iot['dev'] == 171)) & (df_iot['num'] > 20000))]
    df_iot = df_iot[['dev','num','receive_time']]
    #----对于今日更换袜子的袜机----#
    df_iot.columns = ['device_id','num','receive_time']
    #----对于今日没有更换袜子的袜机----#
    df_socks_pre['start_time'] = zero_today
    df_socks_pre['end_time'] = now

    #若df_socks_today 为空
    if df_socks_today.empty:
        df_socks = df_socks_pre
    else:
        # ----对于今日更换袜子的袜机----#

        # 将每个device的初始和末尾数据补上
        print(df_socks_today, df_socks_today.columns)
        for device_id in df_socks_today['device_id'].unique():
            goods_id = df_socks_today.loc[df_socks_today['device_id'] == device_id].iloc[0]['goods_id_old']
            goods_style = df_socks_today.loc[df_socks_today['device_id'] == device_id].iloc[0]['goods_style_old']
            batch_id = df_socks_today.loc[df_socks_today['device_id'] == device_id].iloc[0]['batch_id_old']
            df_socks_today.loc[len(df_socks_today)] = [device_id, zero_today, goods_id, goods_style, batch_id, None,
                                                       None, None]
            df_socks_today.loc[len(df_socks_today)] = [device_id, now, None, None, None, None, None, None]
        df_socks_today['operate_time'] = pd.to_datetime(df_socks_today['operate_time'])
        df_socks_today = df_socks_today.sort_values(['device_id', 'operate_time']).reset_index(drop=True)
        df_socks_today['start_time'] = df_socks_today['operate_time']

        # 检查每一行，如果下一行的device_id与当前行相同，则更新当前行的end_time为下一行的operate_time
        for index in df_socks_today.index[:-1]:  # 最后一个元素没有下一行，所以不需要检查
            if df_socks_today.at[index, 'device_id'] == df_socks_today.at[index + 1, 'device_id']:
                df_socks_today.at[index, 'end_time'] = df_socks_today.at[index + 1, 'operate_time']
        # 去掉没有end_time的行
        df_socks_today = df_socks_today[df_socks_today['end_time'].notnull()][
            ['device_id', 'goods_id', 'goods_style', 'batch_id', 'start_time', 'end_time']]
        # 合并df_socks_today 和df_socks_pre
        df_socks = pd.concat([df_socks_today, df_socks_pre], ignore_index=True)

    df_socks = df_socks.sort_values(['device_id','start_time']).reset_index(drop=True)
    df_socks['num'] = None
    #计算每个时间段的袜子产量
    for index, row in df_socks.iterrows():
        device_id = row['device_id']
        start_time = row['start_time']
        end_time = row['end_time']

        # 筛选df_iot中的数据
        mask = (df_iot['device_id'] == device_id) & (df_iot['receive_time'] >= start_time) & (
                    df_iot['receive_time'] <= end_time)
        filtered_data = df_iot[mask]

        # 如果筛选后的数据不为空，找到最早和最晚的记录
        if not filtered_data.empty:
            earliest_record = filtered_data.loc[filtered_data['receive_time'].idxmin()]
            latest_record = filtered_data.loc[filtered_data['receive_time'].idxmax()]

            # 计算最早和最晚记录的num值之差
            num_diff = latest_record['num'] - earliest_record['num']

            # 将num值之差写入到df_socks_combined对应行的num列中
            df_socks.at[index, 'num'] = num_diff
    # df_socks_today['date'] = today

    df_socks = df_socks[df_socks['num']>0]
    df_socks_production = df_socks[['goods_id', 'goods_style','batch_id','num']]
    df_socks_production['num'] = df_socks_production['num'].apply(lambda x: round((x+1)/2.0))
    df_socks_production = df_socks_production.groupby(['goods_id', 'goods_style','batch_id']).sum().reset_index()
    df_socks_device = df_socks_device[df_socks_device['avg_time']>0]
    df_socks_device['device_num'] = 1
    df_socks_device['capacity'] = df_socks_device['avg_time'].apply(lambda x: round(43200/2/x*0.95))
    df_socks_device.drop(columns=['avg_time'], inplace=True)
    df_socks_device = df_socks_device.groupby(['goods_id', 'goods_style','batch_id']).sum().reset_index()
    df_socks_device = df_socks_device[['goods_id', 'goods_style','batch_id','device_num','capacity']]
    df_socks_production = pd.merge(df_socks_production, df_socks_device, on=['goods_id', 'goods_style','batch_id'],how='left')
    # df_socks_production_devnum.to_csv('df_socks_production_devnum.csv', index=False,encoding='ANSI')
    # df_socks_production_num.to_csv('df_socks_production.csv', index=False,encoding='ANSI')
    df_socks_production['date'] = today
    #
    # print(df_socks_production.head(50),df_socks_production.columns)
    # print(df_socks_device.head(50),df_socks_device.columns)
    with connect.cursor() as cursor:
        sql = f"DELETE FROM mm_new_socks_production WHERE date = %s"
        today_str = today.strftime('%Y-%m-%d')  # Format today's date as a string
        cursor.execute(sql, (today_str,))
        connect.commit()
    df_socks_production.to_sql('mm_new_socks_production', con=conn2, if_exists='append', index=False)

while True:
    # 遍历过去10天（从昨天开始，到10天前）
    try:
        now = datetime.now()
        for i in range(1, 11):  # 1到10天前
            past_date = now - timedelta(days=i)
            socks_production(past_date)  # 直接传入datetime对象
        # socks_production(now)
        # print(now)
    except Exception as e:
        print(f"An error occurred: {e}")
    # 每隔10分钟执行一次
    time.sleep(600)


















































































































































































































































































































































































































































































































































































